package com.leosoft.heatmonitor.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Component;

import com.leosoft.heatmonitor.model.Device;
import com.leosoft.heatmonitor.model.Project;
import com.leosoft.heatmonitor.model.Target;

@Component
public class ProjectDAO extends BaseDAO {

	public Map<String, Object> list(int startIndex, int length, List<String[]> wherePart, List<String[]> odrerPart,
			String userId) {
		Criteria c = this.getSession().createCriteria(Project.class);
		if (!userId.isEmpty()) {
			c = c.createAlias("users", "u");
			c = c.add(Restrictions.eq("u.id", userId));
		}
		c.setProjection(Projections.rowCount());
		Object total = c.uniqueResult();

		for (String[] order : odrerPart) {
			if (order[1].equals("desc")) {
				c.addOrder(Order.desc(order[0]));
			} else {
				c.addOrder(Order.asc(order[0]));
			}
		}
		for (String[] where : wherePart) {
			c = c.add(Restrictions.like(where[0], where[1], MatchMode.ANYWHERE));
		}

		Object recordsFiltered = c.uniqueResult();
		c.setProjection(null);
		c.setFirstResult(startIndex);
		c.setMaxResults(length);
		c.setResultTransformer(Criteria.ROOT_ENTITY);
		List<Project> projects = c.list();
		List<Map<String, String>> datas = new ArrayList<>();
		for (Project project : projects) {

			Map<String, String> data = new HashMap<>();
			data.put("id", project.getId());
			data.put("name", project.getName());
			data.put("province", project.getProvince());
			data.put("city", project.getCity());
			data.put("district", project.getDistrict());
			data.put("description", project.getDescription());
			data.put("rate", project.getRate().toString());
			datas.add(data);
		}
		Map<String, Object> result = new HashMap<String, Object>();
		result.put("recordsTotal", total);
		result.put("recordsFiltered", recordsFiltered);
		result.put("data", datas);
		return result;
	}

	public boolean notExist(String name, String province, String city, String district) {
		Criteria c = this.getSession().createCriteria(Project.class);
		c = c.add(Restrictions.eq("name", name));
		c = c.add(Restrictions.eq("province", province));
		c = c.add(Restrictions.eq("city", city));
		c = c.add(Restrictions.eq("district", district));
		return c.list().isEmpty();
	}

	public void create(String name, String description, String province, String city, String district, Double rate) {
		Project project = new Project();
		project.setName(name);
		project.setDescription(description);
		project.setProvince(province);
		project.setCity(city);
		project.setDistrict(district);
		project.setUsers(null);
		project.setRate(rate);
		this.getSession().merge(project);

	}

	public Project getProjectById(String id) {
		return (Project) this.getSession().get(Project.class, id);
	}

	public void update(String id, String name, String description, String province, String city, String district,
			Double rate) {
		Project project = getProjectById(id);
		project.setName(name);
		project.setDescription(description);
		project.setProvince(province);
		project.setCity(city);
		project.setDistrict(district);
		project.setRate(rate);
		this.getSession().merge(project);
	}

	public void delete(String id) {
		Project project = getProjectById(id);
		this.getSession().delete(project);
		SQLQuery q1 = this.getSession().createSQLQuery("delete from target where project_Id='"+id+"'");
		q1.executeUpdate();
		SQLQuery q2 = this.getSession().createSQLQuery("delete from device where project_Id='"+id+"'");
		q2.executeUpdate();
		SQLQuery q3 = this.getSession().createSQLQuery("delete from devicetarget where target_Id not in(select id from target) or device_id not in(select id from device)");
		q3.executeUpdate();
		SQLQuery q4 = this.getSession().createSQLQuery("delete from control where project_Id='"+id+"'");
		q4.executeUpdate();

	}


	public List<Map<String, Object>> loadProject(String userId) {
		Criteria c = this.getSession().createCriteria(Project.class);
		if (!userId.isEmpty()) {
			c = c.createCriteria("users");
			c = c.add(Restrictions.eq("id", userId));
		}
		List<Project> projects = c.list();
		List<Map<String, Object>> result = new ArrayList<>();
		for (Project project : projects) {
			int flagProvince = 0;
			for (Map<String, Object> province : result) {
				if (province.get("title").equals(project.getProvince())) {
					List<Map<String, Object>> citys = (List<Map<String, Object>>) province.get("children");
					int flagCity = 0;
					for (Map<String, Object> city : citys) {
						if (city.get("title").equals(project.getCity())) {
							List<Map<String, Object>> districts = (List<Map<String, Object>>) city.get("children");
							int flagDistrict = 0;
							for (Map<String, Object> district : districts) {
								if (district.get("title").equals(project.getDistrict())) {
									List<Map<String, Object>> children = (List<Map<String, Object>>) district
											.get("children");
									Map<String, Object> pj = new HashMap<>();
									pj.put("title", project.getName());
									pj.put("key", project.getId());
									children.add(pj);
									district.put("children", children);
									districts.remove(district);
									districts.add(district);
									flagDistrict = 1;
									break;
								}
							}
							if (flagDistrict == 0) {
								Map<String, Object> pj = new HashMap<>();
								pj.put("title", project.getName());
								pj.put("key", project.getId());
								List<Map<String, Object>> children = new ArrayList<>();
								children.add(pj);
								Map<String, Object> district = new HashMap<>();
								district.put("title", project.getDistrict());
								district.put("children", children);
								district.put("isFolder", true);
								districts.add(district);
							}
							city.put("children", districts);
							citys.remove(city);
							citys.add(city);
							flagCity = 1;
							break;
						}
					}
					if (flagCity == 0) {
						Map<String, Object> pj = new HashMap<>();
						pj.put("title", project.getName());
						pj.put("key", project.getId());
						List<Map<String, Object>> children = new ArrayList<>();
						children.add(pj);
						Map<String, Object> district = new HashMap<>();
						district.put("title", project.getDistrict());
						district.put("children", children);
						district.put("isFolder", true);
						List<Map<String, Object>> districts = new ArrayList<>();
						districts.add(district);
						Map<String, Object> city = new HashMap<>();
						city.put("title", project.getCity());
						city.put("children", districts);
						city.put("isFolder", true);
						citys.add(city);
					}
					province.put("children", citys);
					result.remove(province);
					result.add(province);
					flagProvince = 1;
					break;
				}
			}
			if (flagProvince == 0) {
				Map<String, Object> pj = new HashMap<>();
				pj.put("title", project.getName());
				pj.put("key", project.getId());
				List<Map<String, Object>> children = new ArrayList<>();
				children.add(pj);
				Map<String, Object> district = new HashMap<>();
				district.put("title", project.getDistrict());
				district.put("children", children);
				district.put("isFolder", true);
				List<Map<String, Object>> districts = new ArrayList<>();
				districts.add(district);
				Map<String, Object> city = new HashMap<>();
				city.put("title", project.getCity());
				city.put("children", districts);
				city.put("isFolder", true);
				List<Map<String, Object>> citys = new ArrayList<>();
				citys.add(city);
				Map<String, Object> province = new HashMap<>();
				province.put("title", project.getProvince());
				province.put("children", citys);
				province.put("isFolder", true);
				result.add(province);
			}
		}
		return result;
	}

	public List<String> suggestProject(String projectName, String userId) {
		Criteria c = this.getSession().createCriteria(Project.class);
		c.add(Restrictions.like("name", projectName, MatchMode.START));
		c.setProjection(Projections.groupProperty("name"));
		if (!userId.isEmpty()) {
			c = c.createCriteria("users");
			c = c.add(Restrictions.eq("id", userId));
		}
		return c.list();
	}

	public Object dashBoard(String userId) {
		String sql = "SELECT count(distinct project.id) AS projectCount, count(distinct device.id) AS deviceCount, count(DISTINCT CONCAT(project.id,target.building)) AS buildingCount, count(distinct target.id) AS targetCount FROM project, device, target, devicetarget ";
		if (!userId.isEmpty()) {
			sql += ", control";
		}
		sql += " WHERE device.project_id = project.id AND device.id = devicetarget.device_id AND target.id = devicetarget.target_id";
		if (!userId.isEmpty()) {
			sql += " and control.project_id = project.id and control.user_id='" + userId + "'";
		}
		return this.getSession().createSQLQuery(sql).uniqueResult();
	}
}
